//
//  SYClassesTool.m
//  ChatDemo-UI2.0
//
//  Created by zz on 16/8/17.
//  Copyright © 2016年 zz. All rights reserved.
//

#import "SYClassesTool.h"
#import "homework.h"

NSString *const lastAppVersion = @"lastAppVersion";

@implementation SYClassesTool

static FMDatabase *_db;

+(void)initialize{
    
    //获取文件路径
    NSString *path = NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES).lastObject;
    
    path = [path stringByAppendingPathComponent:USERID];
    
    NSFileManager *manager = [NSFileManager defaultManager];
    BOOL tag,flag;
    if (![manager fileExistsAtPath:path isDirectory:&flag]) {
        
        if (!flag) {
            
            NSError *err;
            tag = [manager createDirectoryAtPath:path withIntermediateDirectories:YES attributes:nil error:&err];
            NSLog(@"%@",err);
        }
        
    }
    
    
    path = [path stringByAppendingPathComponent:@"user.db"];
    
    
    _db = [FMDatabase databaseWithPath:path];
    
    if (![_db open]) {
        
        NSLog(@"数据库打开失败");
    }
    
    NSString * buildVersion = [[[NSBundle mainBundle] infoDictionary] objectForKey:@"CFBundleVersion"];
    
    NSString *oldVersion = [[NSUserDefaults standardUserDefaults] objectForKey:lastAppVersion];
    
    if (!oldVersion || ![buildVersion isEqualToString:oldVersion]) {
        
        [_db executeUpdate:@"drop table if exists t_notification_teacher"];
        [_db executeUpdate:@"drop table if exists t_notification_parents"];
        [_db executeUpdate:@"drop table if exists t_homework_Detail"];
        [_db executeUpdate:@"drop table if exists t_homework_teacher"];
        [_db executeUpdate:@"drop table if exists t_homework_parents"];
    }
    
    [[NSUserDefaults standardUserDefaults] setObject:buildVersion forKey:lastAppVersion];
    [[NSUserDefaults standardUserDefaults] synchronize];
    
    //[_db executeQuery:@"create table if not exists t_classes (id integer primary key autoincrement,classid integer,gradeid,integer,courseid text,scid,integer,classname,text,state,integer,jointime,text,userid,integer);"];
    
    //创建老师身份通知表
    [_db executeUpdate:@"create table if not exists t_notification_teacher (id integer primary key autoincrement,infoid integer,userid integer,title text,scontent text,fileid text,type integer,state integer,classname text,username text,ismyPub integer,tousername text,isread integer,readnum integer,allnum integer,time text,week text,classid text,studentname text,studentid text,coursename text);"];
    
    //创建家长身份的通知表
    [_db executeUpdate:@"create table if not exists t_notification_parents (id integer primary key autoincrement,infoid integer,userid integer,title text,scontent text,fileid text,type integer,state integer,classname text,username text,ismyPub integer,tousername text,isread integer,readnum integer,allnum integer,time text,week text,classid text,studentname text,studentid text,coursename text);"];
    

    //创建作业表
    
    //创建作业详情表
    [_db executeUpdate:@"CREATE TABLE IF NOT EXISTS t_homework_Detail (id integer PRIMARY KEY AUTOINCREMENT, infoId integer, detailData blob);"];
    

    //创建老师身份作业表
    [_db executeUpdate:@"create table if not exists t_homework_teacher (id integer primary key autoincrement,infoid integer,userid integer,title text,scontent text,fileid text,type integer,state integer,classname text,username text,ismyPub integer,tousername text,isread integer,readnum integer,allnum integer,time text,week text,coursename text,isfinish integer,classid text,studentname text,studentid text);"];
    
    //创建家长身份作业表
    [_db executeUpdate:@"create table if not exists t_homework_parents (id integer primary key autoincrement,infoid integer,userid integer,title text,scontent text,fileid text,type integer,state integer,classname text,username text,ismyPub integer,tousername text,isread integer,readnum integer,allnum integer,time text,week text,coursename text,isfinish integer,studentname text,studentid text,classid text);"];
}

#pragma mark ==================清楚缓存================
+(void)cleanDisk{
    
    [_db executeUpdate:@"delete from t_homework_parents"];
    [_db executeUpdate:@"delete from t_homework_teacher"];
    [_db executeUpdate:@"t_notification_parents"];
    [_db executeUpdate:@"t_notification_teacher"];

}

#pragma mark ==================通知====================

+(NSInteger)getTeacherFirstNotification{
    
    NSString *sql = [NSString stringWithFormat:@"select  * from t_notification_teacher order by infoid desc"];
    
    FMResultSet *set = [_db executeQuery:sql];
    
    int maxInfoid = 0;
    int infoid;
    
    while ([set next]) {
        
        infoid = [set intForColumn:@"infoid"];
        if (infoid > maxInfoid) {
            
            maxInfoid = infoid;
        }
    }
    
    return maxInfoid;
}

+(BOOL)saveTeacherNotificationWithArray:(NSArray *)array{
    
    
    NSString *sql;
    
    for (NSDictionary *dic in array) {
        
        homework *model = [homework mj_objectWithKeyValues:dic];
        
        int infoid = [model.infoid intValue];
        int type = (int)model.type;
        int state = (int)model.state;
        NSString *title = model.title;
        NSString *content = model.scontent;
        NSString *fileid = model.fileid;
        NSString *classnaem = model.classname;
        NSString *usernaem = model.username;
        
        //BOOL flag = [_db executeUpdate:@"insert into t_notification_teacher (infoid,title,scontent,fileid,type,state) values (?,?,?,?,?,?)",infoid,title,content,fileid,type,state];
        
        sql = [NSString stringWithFormat:@"insert into t_notification_teacher(infoid,title,scontent,fileid,type,state,classname,username) values(%d,\"%@\",\"%@\",\"%@\",%d,%d,\"%@\",\"%@\");",infoid,title,content,fileid,type,state,classnaem,usernaem];
        
        [_db executeUpdate:sql];
        
    }
    
    return YES;
}

+(BOOL)saveTeacherNotificationWithDict:(NSDictionary *)dict withType:(NSString *)typeStr{
    
    int infoid = [[dict objectForKey:@"infoid"] intValue];
    int type = [[dict objectForKey:@"type"] intValue];
    int state = [[dict objectForKey:@"state"] intValue];
    NSString *title = [dict objectForKey:@"title"];
    NSString *content = [dict objectForKey:@"scontent"];
    NSString *fileid = [dict objectForKey:@"fileid"];
    NSString *classname = [dict objectForKey:@"classname"];
    NSString *username = [dict objectForKey:@"username"];
    NSString *classid = [dict objectForKey:@"classid"];
    /*ismyPub integer,tousername text,isread integer,readnum integer,allnum integer*/
    int ismypub = [[dict objectForKey:@"ismyPub"] intValue];
    NSString *tousername = [dict objectForKey:@"tousername"];
    int isread = [[dict objectForKey:@"isread"] intValue];
    int readnum = [[dict objectForKey:@"readnum"] intValue];
    int allnum = [[dict objectForKey:@"allnum"] intValue];
    NSString *time = [dict objectForKey:@"jointime"];
    NSString *week = [dict objectForKey:@"week"];
    NSString *studentname = [dict objectForKey:@"studentname"];
    NSString *studentid = [dict objectForKey:@"studentid"];
    NSString *coursename = [dict objectForKey:@"coursename"];
    

    NSString *sqls = [NSString stringWithFormat:@"delete from t_notification_%@ where infoid = %d and classid = %@",typeStr,infoid,classid];
    [_db executeUpdate:sqls];
    

    
    NSString *sql = [NSString stringWithFormat:@"insert into t_notification_%@(infoid,title,scontent,fileid,type,state,classname,username,ismypub,tousername,isread,readnum,allnum,time,week,classid,studentname,studentid,coursename) values(%d,\"%@\",\"%@\",\"%@\",%d,%d,\"%@\",\"%@\",%d,\"%@\",%d,%d,%d,\"%@\",\"%@\",\"%@\",\"%@\",\"%@\",\"%@\");",typeStr,infoid,title,content,fileid,type,state,classname,username,ismypub,tousername,isread,readnum,allnum,time,week,classid,studentname,studentid,coursename];
    return [_db executeUpdate:sql];

}

+(NSMutableArray *)getTeacherNotificationWithType:(NSString *)typestr{
    
    NSString *sql = [NSString stringWithFormat:@"select *from t_notification_%@ order by infoid desc limit 10",typestr];
    
    FMResultSet *set = [_db executeQuery:sql];
    
    NSMutableArray *arr = [NSMutableArray array];
    
    while ([set next]) {
        
        int infoid = [set intForColumn:@"infoid"];
        int type = [set intForColumn:@"type"];
        int state = [set intForColumn:@"state"];
        NSString *title = [set stringForColumn:@"title"];
        NSString *content = [set stringForColumn:@"scontent"];
        NSString *fileid = [set stringForColumn:@"fileid"];
        NSString *classname = [set stringForColumn:@"classname"];
        NSString *username = [set stringForColumn:@"username"];
        int ismypub = [set intForColumn:@"ismypub"];;
        NSString *tousername = [set stringForColumn:@"tousername"];
        int isread = [set intForColumn:@"isread"];
        int readnum = [set intForColumn:@"readnum"];
        int allnum = [set intForColumn:@"allnum"];
        NSString *time = [set stringForColumn:@"time"];
        NSString *week = [set stringForColumn:@"week"];
        NSString *classid = [set stringForColumn:@"classid"];
        NSString *studentname = [set stringForColumn:@"studentname"];
        NSString *studentid = [set stringForColumn:@"studentid"];
        NSString *coursename = [set stringForColumn:@"coursename"];

        
        homework *model = [[homework alloc] init];
        model.infoid = [NSString stringWithFormat:@"%d",infoid];
        model.type = type;
        model.state = state;
        model.title = title;
        model.scontent = content;
        model.fileid = fileid;
        model.classname = classname;
        model.username = username;
        model.ismyPub = ismypub;
        model.tousername = tousername;
        if ([typestr isEqualToString:@"teacher"]) {
            
            isread = 1;
        }
        model.isread = [NSString stringWithFormat:@"%d",isread];
        model.readnum = readnum;
        model.allnum = allnum;
        model.jointime = time;
        model.week = week;
        model.classid = classid;
        model.studentid = studentid;
        model.studentname = studentname;
        model.coursename = coursename;
        
        [arr addObject:model];
    }
    
    return arr;
    
}

//上拉加载更多数据
+(NSMutableArray *)getMoreDataWithinfoid:(NSInteger)infoid andTypeStr:(NSString *)typeStr{
    
    NSString *sql = [NSString stringWithFormat:@"select  * from t_notification_%@ where infoid < %d order by infoid desc limit 10",typeStr,(int)infoid];
    FMResultSet *set = [_db executeQuery:sql];
    
    
    NSMutableArray *arr = [NSMutableArray array];
    while ([set next]) {
        
        int infoid = [set intForColumn:@"infoid"];
        int type = [set intForColumn:@"type"];
        int state = [set intForColumn:@"state"];
        NSString *title = [set stringForColumn:@"title"];
        NSString *content = [set stringForColumn:@"scontent"];
        NSString *fileid = [set stringForColumn:@"fileid"];
        NSString *classname = [set stringForColumn:@"classname"];
        NSString *username = [set stringForColumn:@"username"];
        int ismypub = [set intForColumn:@"ismypub"];;
        NSString *tousername = [set stringForColumn:@"tousername"];
        int isread = [set intForColumn:@"isread"];
        int readnum = [set intForColumn:@"readnum"];
        int allnum = [set intForColumn:@"allnum"];
        NSString *time = [set stringForColumn:@"time"];
        NSString *week = [set stringForColumn:@"week"];
        NSString *classid = [set stringForColumn:@"classid"];
        NSString *studentname = [set stringForColumn:@"studentname"];
        NSString *studentid = [set stringForColumn:@"studentid"];
        NSString *coursename = [set stringForColumn:@"coursename"];
        
        homework *model = [[homework alloc] init];
        model.infoid = [NSString stringWithFormat:@"%d",infoid];
        model.type = type;
        model.state = state;
        model.title = title;
        model.scontent = content;
        model.fileid = fileid;
        model.classname = classname;
        model.username = username;
        model.ismyPub = ismypub;
        model.tousername = tousername;
        if ([typeStr isEqualToString:@"teacher"]) {
            
            isread = 1;
        }
        
        model.isread = [NSString stringWithFormat:@"%d",isread];
        model.readnum = readnum;
        model.allnum = allnum;
        model.jointime = time;
        model.week = week;
        model.classid = classid;
        model.studentname = studentname;
        model.studentid = studentid;
        model.coursename = coursename;
        [arr addObject:model];
    }
    
    return arr;
}



+ (BOOL)saveCacheDataWithNSArr:(NSArray *)cacheDataArr withInfoID:(NSString *)infoID {
    
    for (NSDictionary *statusDict in cacheDataArr) {
        
        NSString *sql = [NSString stringWithFormat:@"select *from t_homework_Detail where infoId = %@ order by infoid desc limit 10",infoID];
        
        FMResultSet *set = [_db executeQuery:sql];
        
        BOOL flag = false;
        while ([set next]) {
            
            flag = YES;
        }
        
        if (!flag) {
            
            // 把字典对象序列化成NSData二进制数据
            NSData *data = [NSKeyedArchiver archivedDataWithRootObject:statusDict];
            [_db executeUpdate:@"INSERT INTO t_homework_Detail (infoId, detailData) VALUES (?, ?);",
             infoID, data];
        }
    }
    return YES;
}

+ (NSMutableArray *)getCacheDataFromInfoID:(NSString *)infoID {
    
    NSString *sql = [NSString stringWithFormat:@"select *from t_homework_Detail where infoId = %@ order by infoid desc limit 10",infoID];
    
    FMResultSet *set = [_db executeQuery:sql];
    
    NSMutableArray *arr = [NSMutableArray array];
    
    
    while (set.next) {
        NSData *statusDictData = [set objectForColumnName:@"detailData"];
        NSDictionary *statusDict = [NSKeyedUnarchiver unarchiveObjectWithData:statusDictData];
        
        [arr addObject:statusDict];
    }
    
    
    return arr;
}

/**标记所有为已读状态*/
+ (void)setAllNotificationAndHomeworkIsread:(notificationType)type{

    NSString *str = type == 0 ? @"notification" : @"homework";
    
    NSString *sql = [NSString stringWithFormat:@"update t_%@_parents set isread = 1",str];
    
    [_db executeUpdate:sql];
}

/**标记选中为已读状态*/
+ (void)setNotificationAndHomeworkAsRead:(NSArray *)selectNotis Notificationtype:(notificationType)type {
    
    NSString *str = type == 0 ? @"notification" : @"homework";
    
    for (homework *model in selectNotis) {
        
        NSString *infoId = model.infoid;
        
        NSString *sql = [NSString stringWithFormat:@"update t_%@_parents set isread = 1 where infoid = ?",str];
        [_db executeUpdate:sql,infoId];
    }
}

+ (void)deleteNotificationAndHomework:(NSArray *)selectNotis Notificationtype:(notificationType)type{
    
    NSString *str = type == 0 ? @"notification" : @"homework";
    
    for (homework *model in selectNotis) {
        
        NSString *infoId = model.infoid;
        NSString *sql = [NSString stringWithFormat:@"delete from t_%@_parents where infoid = ?",str];
        [_db executeUpdate:sql,infoId];
    }
}

#pragma mark =================作业===================
//存
+(BOOL)saveTeacherHomeWorkWithDict:(NSDictionary *)dict withType:(NSString *)typeStr{
    
    int infoid = [[dict objectForKey:@"infoid"] intValue];
    int type = [[dict objectForKey:@"type"] intValue];
    int state = [[dict objectForKey:@"state"] intValue];
    NSString *title = [dict objectForKey:@"title"];
    NSString *content = [dict objectForKey:@"scontent"];
    NSString *fileid = [dict objectForKey:@"fileid"];
    NSString *classname = [dict objectForKey:@"classname"];
    NSString *username = [dict objectForKey:@"username"];
    /*ismyPub integer,tousername text,isread integer,readnum integer,allnum integer*/
    int ismypub = [[dict objectForKey:@"ismyPub"] intValue];
    NSString *tousername = [dict objectForKey:@"tousername"];
    int isread = [[dict objectForKey:@"isread"] intValue];
    int readnum = [[dict objectForKey:@"readnum"] intValue];
    int allnum = [[dict objectForKey:@"allnum"] intValue];
    NSString *time = [dict objectForKey:@"jointime"];
    NSString *week = [dict objectForKey:@"week"];
    NSString *courname = [dict objectForKey:@"coursename"];
    NSString *classid = [dict objectForKey:@"classid"];
    int isfinish = [[dict objectForKey:@"isfinish"] intValue];
    NSString *studentname = [dict objectForKey:@"studentname"];
    NSString *studentid = [dict objectForKey:@"studentid"];
    NSString *coursename = [dict objectForKey:@"coursename"];

    
    NSString *deleteSql = [NSString stringWithFormat:@"delete from t_homework_%@ where infoid = %d",typeStr,infoid];
    [_db executeUpdate:deleteSql];
    
    NSString *sql = [NSString stringWithFormat:@"insert into t_homework_%@(infoid,title,scontent,fileid,type,state,classname,username,ismypub,tousername,isread,readnum,allnum,time,week,coursename,isfinish,classid,studentname,studentid,coursename) values(%d,\"%@\",\"%@\",\"%@\",%d,%d,\"%@\",\"%@\",%d,\"%@\",%d,%d,%d,\"%@\",\"%@\",\"%@\",%d,\"%@\",\"%@\",\"%@\",\"%@\");",typeStr,infoid,title,content,fileid,type,state,classname,username,ismypub,tousername,isread,readnum,allnum,time,week,courname,isfinish,classid,studentname,studentid,coursename];
    return [_db executeUpdate:sql];

}

//取
+(NSMutableArray *)getTeacherHomeWorkWithType:(NSString *)typestr{
    

    NSString *sql = [NSString stringWithFormat:@"select *from t_homework_%@ order by infoid desc limit 10",typestr];
    

    FMResultSet *set = [_db executeQuery:sql];
    
    NSMutableArray *arr = [NSMutableArray array];
    
    while ([set next]) {
        
        int infoid = [set intForColumn:@"infoid"];
        int type = [set intForColumn:@"type"];
        int state = [set intForColumn:@"state"];
        NSString *title = [set stringForColumn:@"title"];
        NSString *content = [set stringForColumn:@"scontent"];
        NSString *fileid = [set stringForColumn:@"fileid"];
        NSString *classname = [set stringForColumn:@"classname"];
        NSString *username = [set stringForColumn:@"username"];
        int ismypub = [set intForColumn:@"ismypub"];;
        NSString *tousername = [set stringForColumn:@"tousername"];
        int isread = [set intForColumn:@"isread"];
        int readnum = [set intForColumn:@"readnum"];
        int allnum = [set intForColumn:@"allnum"];
        NSString *time = [set stringForColumn:@"time"];
        NSString *week = [set stringForColumn:@"week"];
        NSString *coursename = [set stringForColumn:@"coursename"];
        int isfinish = [set intForColumn:@"isfinish"];
        NSString *classid = [set stringForColumn:@"classid"];
        NSString *studentname = [set stringForColumn:@"studentname"];
        NSString *studentid = [set stringForColumn:@"studentid"];
        
        homework *model = [[homework alloc] init];
        model.infoid = [NSString stringWithFormat:@"%d",infoid];
        model.type = type;
        model.state = state;
        model.title = title;
        model.scontent = content;
        model.fileid = fileid;
        model.classname = classname;
        model.username = username;
        model.ismyPub = ismypub;
        model.tousername = tousername;
        
        if ([typestr isEqualToString:@"teacher"]) {
            
            isread = 1;
        }

        model.isread = [NSString stringWithFormat:@"%d",isread];
        model.readnum = readnum;
        model.allnum = allnum;
        model.jointime = time;
        model.week = week;
        model.coursename = [coursename isEqualToString:@"<null>"] ? @"" : coursename;
        model.isfinish = isfinish;
        model.classid = classid;
        model.studentid = studentid;
        model.studentname = studentname;
        
        [arr addObject:model];
    }
    
    return arr;

}

+(NSMutableArray *)getMoreHomeworkDataWithinfoid:(NSInteger)infoid andTypeStr:(NSString *)typeStr{
    
    NSString *sql = [NSString stringWithFormat:@"select  * from t_homework_%@ where infoid < %d order by infoid desc limit 10",typeStr,(int)infoid];
    FMResultSet *set = [_db executeQuery:sql];
    
    
    NSMutableArray *arr = [NSMutableArray array];
    while ([set next]) {
        
        int infoid = [set intForColumn:@"infoid"];
        int type = [set intForColumn:@"type"];
        int state = [set intForColumn:@"state"];
        NSString *title = [set stringForColumn:@"title"];
        NSString *content = [set stringForColumn:@"scontent"];
        NSString *fileid = [set stringForColumn:@"fileid"];
        NSString *classname = [set stringForColumn:@"classname"];
        NSString *username = [set stringForColumn:@"username"];
        int ismypub = [set intForColumn:@"ismypub"];;
        NSString *tousername = [set stringForColumn:@"tousername"];
        int isread = [set intForColumn:@"isread"];
        int readnum = [set intForColumn:@"readnum"];
        int allnum = [set intForColumn:@"allnum"];
        NSString *time = [set stringForColumn:@"time"];
        NSString *coursename = [set stringForColumn:@"coursename"];
        int isfinish = [set intForColumn:@"isfinish"];
        NSString *classid = [set stringForColumn:@"classid"];
        NSString *studentname = [set stringForColumn:@"studentname"];
        NSString *studentid = [set stringForColumn:@"studentid"];
        
        
        homework *model = [[homework alloc] init];
        model.infoid = [NSString stringWithFormat:@"%d",infoid];
        model.type = type;
        model.state = state;
        model.title = title;
        model.scontent = content;
        model.fileid = fileid;
        model.classname = classname;
        model.username = username;
        model.ismyPub = ismypub;
        model.tousername = tousername;
        if ([typeStr isEqualToString:@"teacher"]) {
            
            isread = 1;
        }
        model.isread = [NSString stringWithFormat:@"%d",isread];
        model.readnum = readnum;
        model.allnum = allnum;
        model.jointime = time;
        model.coursename = coursename;
        model.isfinish = isfinish;
        model.classid = classid;
        model.studentname = studentname;
        model.studentid = studentid;
        
        [arr addObject:model];
    }
    
    return arr;

}




@end
